
drop table if exists canyin.tb_object_sync_log;

drop table if exists canyin.tb_order_partition;

drop table if exists canyin.tb_prepare_waiting;

drop table if exists canyin.tb_prepare_window;

drop table if exists canyin.tb_sync_status;

drop table if exists canyin.tb_sys_parameter;

drop table if exists canyin.tb_user_token;

/*==============================================================*/
/* Table: tb_object_sync_log                                    */
/*==============================================================*/
create table canyin.tb_object_sync_log (
   id                   SERIAL not null,
   object_id            bigint               not null,
   object_type          int                  not null,
   oper_type            char(1)              not null,
   create_time          timestamp            not null,
   constraint PK_TB_OBJECT_SYNC_LOG primary key (id)
);

comment on column canyin.tb_object_sync_log.object_type is
'1：订单；2：订单项；';

comment on column canyin.tb_object_sync_log.oper_type is
'U：更新；I：插入；';

/*==============================================================*/
/* Index: idx_sync_log_object                                   */
/*==============================================================*/
create  index idx_sync_log_object on canyin.tb_object_sync_log (
object_id,
object_type
);

/*==============================================================*/
/* Table: tb_order_partition                                    */
/*==============================================================*/
create table canyin.tb_order_partition (
   id                   SERIAL not null,
   bs_id                INT8                 not null,
   point_name           varchar(50)          not null,
   order_time           timestamp            not null,
   status               INT2                 not null,
   schedule_time        TIME                 null,
   constraint PK_TB_ORDER_PARTITION primary key (id)
);

comment on column canyin.tb_order_partition.status is
'1：等待配菜；2：配菜中；3：完成配菜；4：预订中；';

/*==============================================================*/
/* Table: tb_prepare_waiting                                    */
/*==============================================================*/
create table canyin.tb_prepare_waiting (
   id                   SERIAL not null,
   bs_id                INT8                 not null,
   sc_id                INT8                 not null,
   item_id              INT8                 not null,
   item_name            VARCHAR(50)          not null,
   count                INT2                 not null,
   out_tag              INT2                 not null,
   point_id             INT8                 not null,
   point_name           VARCHAR(50)          not null,
   methods              TEXT                 null,
   order_time           timestamp            not null,
   constraint PK_TB_PREPARE_WAITING primary key (id)
);

comment on column canyin.tb_prepare_waiting.out_tag is
'0：堂食；1：外卖；';

/*==============================================================*/
/* Table: tb_prepare_window                                     */
/*==============================================================*/
create table canyin.tb_prepare_window (
   id                   SERIAL not null,
   item_id              INT8                 not null,
   item_name            VARCHAR(50)          not null,
   count                INT2                 not null,
   out_tag              INT2                 not null,
   bs_ids               TEXT                 not null,
   sc_ids               TEXT                 not null,
   table_names          TEXT                 not null,
   methods              TEXT                 null,
   order_times          TEXT                 not null,
   constraint PK_TB_PREPARE_WINDOW primary key (id)
);

comment on column canyin.tb_prepare_window.out_tag is
'0：堂食；1：外卖；';

comment on column canyin.tb_prepare_window.sc_ids is
'关联加单id列表，json数组';

comment on column canyin.tb_prepare_window.table_names is
'json数组，保存该条目对应作为的名称数组';

comment on column canyin.tb_prepare_window.methods is
'json数组，保存该条目对应订单的做法（可能多个，里面又是数组）数组';

comment on column canyin.tb_prepare_window.order_times is
'json数组，保存该条目对应菜品的下单时间数组';

/*==============================================================*/
/* Table: tb_sync_status                                        */
/*==============================================================*/
create table canyin.tb_sync_status (
   object_type          int                  not null,
   last_id              bigint               not null,
   update_time          timestamp            not null,
   constraint PK_TB_SYNC_STATUS primary key (object_type)
);


/*==============================================================*/
/* Index: idx_token_user                                        */
/*==============================================================*/
create  index idx_token_user on canyin.tb_user_token (
user_id
);



insert into canyin.tb_sync_status(object_type, last_id, update_time) values
    (1, 0, now()),
    (2, 0, now()),
    (3, 0, now());

grant all on canyin.tb_object_sync_log to canyin;
grant all on canyin.tb_object_sync_log to postgres;
grant all on canyin.tb_sync_status to canyin;
grant all on canyin.tb_sync_status to postgres;
grant all on canyin.tb_user_token to canyin;
grant all on canyin.tb_user_token to postgres;
grant all on canyin.tb_sys_parameter to canyin;
grant all on canyin.tb_sys_parameter to postgres;
grant all on sequence canyin.tb_object_sync_log_id_seq to postgres;
grant all on sequence canyin.tb_object_sync_log_id_seq to canyin;
DROP TRIGGER IF EXISTS kc_ksc_monitor ON canyin.kc_ksc;

DROP TRIGGER IF EXISTS biz_bs_monitor ON canyin.biz_bs;



-- 给配菜传菜表建立触发器
CREATE OR REPLACE FUNCTION canyin.p_kc_ksc_monitor() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'INSERT') THEN
            --记录需要更新订单记录到云端服务器
            INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.bs_id, 1, 'U', now());
            --记录需要发送新订单通知到配菜终端（新来订单菜品项）
            INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'I', now());
            RETURN NULL;
        ELSIF (TG_OP = 'UPDATE') THEN
            --记录需要更新订单记录到云端服务器
            INSERT INTO tb_object_sync_log(object_id, object_type, oper_type, create_time) values (NEW.bs_id, 1, 'U', now());
            --如果菜品项从"准备中"修改为"已配菜"，则发送通知给传菜终端
            IF (OLD.kitchen_flg = 1 AND NEW.kitchen_flg = 2) THEN
                INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'P', now());
            END IF;
            --如果菜品项从"已配菜"修改为"准备中"，则发送通知给配菜终端
            IF (OLD.kitchen_flg = 2 AND NEW.kitchen_flg = 1) THEN
                INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'I', now());
            END IF;
            --如果菜品项从"已传菜"修改为"已配菜"，则发送通知给传菜终端
            IF (OLD.kitchen_flg = 3 AND NEW.kitchen_flg = 2) THEN
                INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'P', now());
            END IF;
            --如果菜品项数量变少（退单），且处于待配菜状态，则发送通知给所有配菜终端
            IF (OLD.last_qty >  NEW.last_qty  AND OLD.kitchen_flg =1 AND NEW.kitchen_flg =1) THEN
                INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'D', now());
            END IF;
            --如果菜品项数量变少（退单），且处于待传菜状态，则发送通知给所有传菜终端
            IF (OLD.last_qty >  NEW.last_qty  AND OLD.kitchen_flg =2 AND NEW.kitchen_flg =2) THEN
                INSERT INTO tb_object_sync_log (object_id, object_type, oper_type, create_time) values (NEW.id, 2, 'E', now());
            END IF;

            RETURN NULL;
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS kc_ksc_monitor ON canyin.kc_ksc;
CREATE TRIGGER kc_ksc_monitor
AFTER INSERT OR UPDATE ON canyin.kc_ksc
    FOR EACH ROW EXECUTE PROCEDURE canyin.p_kc_ksc_monitor();


-- 给营业流水表建立触发器
CREATE OR REPLACE FUNCTION canyin.p_biz_bs_monitor() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            --记录需要更新订单记录到云端服务器
            INSERT INTO tb_object_sync_log(object_id, object_type, oper_type, create_time) values (NEW.id, 1, 'U', now());
            RETURN NULL;
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS biz_bs_monitor ON canyin.biz_bs;
CREATE TRIGGER biz_bs_monitor
AFTER UPDATE ON canyin.biz_bs
    FOR EACH ROW EXECUTE PROCEDURE canyin.p_biz_bs_monitor();
